﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.SqlClient;
using System.Data.Common;

public partial class DatabaseVerification : System.Web.UI.Page
{
    Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase("Application_Data");

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.lblDesignatedContact.Text = Request.QueryString["PrimaryContactEMail"].ToString();
            BindData();       
        }

        this.btnSubmitVerification.Enabled = this.chkVerify.Checked;        
    }

    protected void BindData()
    {
        if (this.optViewAllOrUnverified.SelectedValue == "All")
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, @"SELECT InstanceName, DatabaseName, CONVERT(varchar,LastVerified,101) AS LastVerified, LastVerifiedBy FROM [Database] Where PrimaryContactEmail = '" + Request.QueryString["PrimaryContactEMail"].ToString() + "' AND DATABASENAME NOT IN ('master', 'model','msdb','tempdb') ORDER BY LastVerified,InstanceName, DatabaseName");

            this.gv.DataSource = ds;
            this.gv.DataBind();  
        }
        else //Expired
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, @"SELECT InstanceName, DatabaseName, CONVERT(varchar,LastVerified,101) AS LastVerified, LastVerifiedBy FROM [Database] Where PrimaryContactEmail = '" + Request.QueryString["PrimaryContactEMail"].ToString() + "' AND DATABASENAME NOT IN ('master', 'model','msdb','tempdb') And (LastVerified < (getutcdate() - 180) or LastVerifiedBy IS NULL) ORDER BY LastVerified ASC, InstanceName, DatabaseName");

            this.gv.DataSource = ds;
            this.gv.DataBind();             
        }

        if (gv.Rows.Count == 0)
        {
            MessageBox.Show("No databases require verification.");
        }
    }

    protected void btnSubmitVerification_Click(object sender, EventArgs e)
    {
        foreach (GridViewRow row in gv.Rows)
        {
            string strInstanceName = gv.DataKeys[row.RowIndex].Values[0].ToString();
            string strDatabaseName = gv.DataKeys[row.RowIndex].Values[1].ToString();

            CheckBox cb = (CheckBox)row.FindControl("RowLevelCheckBox");

            if (cb != null && cb.Checked)
            {
                try
                {
                    DbCommand dbCommand = db.GetSqlStringCommand("UPDATE [Database] SET LastVerified = GETUTCDATE()" +
                                                   ",  LastVerifiedBy = '" + Session["sFullUserName"].ToString() + "'" +
                                                   " WHERE InstanceName = '" + strInstanceName.ToString() + "' and DatabaseName = '" + strDatabaseName.ToString() + "'");

                    db.ExecuteNonQuery(dbCommand);                    
                }
                catch
                {
                    return;
                }
            }
        }        
        Response.Redirect("~/DatabaseVerificationComplete.aspx");        
    }
    protected void optViewAllOr_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindData();
    }
}
